/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package vn.com.sml.SEFM.DAO;

import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import javax.persistence.RollbackException;
import javax.transaction.HeuristicMixedException;
import javax.transaction.HeuristicRollbackException;
import javax.transaction.SystemException;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import vn.com.sml.SEFM.domain.Merchant;
import vn.com.sml.SEFM.domain.Ip;
import vn.com.sml.SEFM.domain.Banks;
import vn.com.sml.SEFM.domain.Transactions;

import vn.com.sml.SEFM.util.HibernateUtil;
/**
 *
 * @author Tunp
 */
public class transactionDAO {
    public static Integer saveTransaction(Transactions trans) throws SecurityException, RollbackException, HeuristicRollbackException, HeuristicMixedException, IllegalStateException, SystemException {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = null;
        Integer TransactionId = null;
        try {
            transaction = (Transaction) session.beginTransaction();
            
            TransactionId = (Integer) session.save(trans);
            transaction.commit();
        }
        catch (HibernateException e){
            transaction.rollback();
            e.printStackTrace();
        }
        finally {
            session.close();
        }
        return TransactionId;
    }
    
    public static List<Transactions> listTransactions() throws RollbackException, HeuristicMixedException, HeuristicRollbackException, SecurityException, IllegalStateException, SystemException {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = null;
        List<Transactions> lst = new ArrayList<Transactions>();
        try {
            transaction = (Transaction) session.beginTransaction();
            
            List lstTransaction = session.createQuery("from Transactions").list();
            for (Iterator iterator = (Iterator) lstTransaction.iterator();iterator.hasNext();)
            {
                
                Transactions trans = (Transactions) iterator.next();
                
                lst.add(trans);
            }
            transaction.commit();
        }
        catch (HibernateException e){
            transaction.rollback();
            e.printStackTrace();
        }
        finally {
            session.close();
        }
        return lst;
    }
    
    public static Transactions getTransactionById(int id)
    {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = null;
        Transactions trans = null;
        try {
            transaction = (Transaction) session.beginTransaction();
            
            trans = (Transactions) session.createQuery("from Transactions where id="+id).uniqueResult();
            
            transaction.commit();
        }
        catch (HibernateException e){
            transaction.rollback();
            e.printStackTrace();
        }
        finally {
            session.close();
        }
        return trans;
    }
    
    public static Boolean DeleteTransaction(int id)
    {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = null;
        Boolean flag = false;
        try {
            transaction = (Transaction) session.beginTransaction();
            Query query = session.createQuery("delete from Transactions as u where id="+id);
            int row = query.executeUpdate();
              if (row == 0){
                  System.out.println("Doesn't deleted any row!");
              }
              else{
                  System.out.println("DeletedRow: " + row);
              }
            flag = true;
            transaction.commit();
        }
        catch (HibernateException e){
            transaction.rollback();
            e.printStackTrace();
        }
        finally {
            session.close();
        }
        return flag;
    }
    
    public static Boolean update(Transactions trans) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = null;
        Boolean flag = false;
        try {
            transaction = (Transaction) session.beginTransaction();
            
            session.update(trans);
            flag = true;
            transaction.commit();
        }
        catch (HibernateException e){
            transaction.rollback();
            e.printStackTrace();
        }
        finally {
            session.close();
        }
        return flag;
    }

    public static List<Transactions> listTransactions(String search_col, String search_text) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = null;
        List<Transactions> lst = new ArrayList<Transactions>();
        if(search_col.equals("Status")) {
            search_col = "Transactionstatus.status";
        }
        else if(search_col.equals("Merchant")){
            search_col = "dvcnt.merchantName";
        }
        try {
            transaction = (Transaction) session.beginTransaction();
            
            List lstTransaction = session.createQuery("from Transactions where " + search_col + " like '%" + search_text + "%'").list();
            for (Iterator iterator = (Iterator) lstTransaction.iterator();iterator.hasNext();)
            {
                Transactions trans = (Transactions) iterator.next();
                
                lst.add(trans);
            }
            transaction.commit();
        }
        catch (HibernateException e){
            transaction.rollback();
            e.printStackTrace();
        }
        finally {
            session.close();
        }
        return lst;
    }
    
    public static List<Transactions> listTransactions(String condition) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = null;
        List<Transactions> lst = new ArrayList<Transactions>();

        try {
            transaction = (Transaction) session.beginTransaction();
            
            List lstTransaction = session.createQuery("from Transactions where " + condition).list();
            for (Iterator iterator = (Iterator) lstTransaction.iterator();iterator.hasNext();)
            {
                Transactions trans = (Transactions) iterator.next();

                lst.add(trans);
            }
            transaction.commit();
        }
        catch (HibernateException e){
            transaction.rollback();
            e.printStackTrace();
        }
        finally {
            session.close();
        }
        return lst;
    }
    
    public static Integer AvgAmountInMonth(String merchantCode, int month, String cardnumber) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = null;
        
        int sum = 0;
        int avg = 0;
        List<Transactions> lst = new ArrayList<Transactions>();
        
        try {
            transaction = (Transaction) session.beginTransaction();
            
            List lstTransaction = session.createQuery("from Transactions").list();
            for (Iterator iterator = (Iterator) lstTransaction.iterator();iterator.hasNext();)
            {
                Transactions trans = (Transactions) iterator.next();
                if(trans.getTransactionDate().getMonth() == month && trans.getCardnumber().equals(cardnumber) && trans.getMerchantCode().equals(merchantCode)) {
                    sum += trans.getAmount();
                }
            }
            avg = sum/30;
            transaction.commit();
        }
        catch (HibernateException e){
            transaction.rollback();
            e.printStackTrace();
        }
        finally {
            session.close();
        }
        return avg;
    }
    
    public static Integer SumAmountInADay(Date date, String cardnumber) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = null;
        
        int sum = 0;
        List<Transactions> lst = new ArrayList<Transactions>();
        
        try {
            transaction = (Transaction) session.beginTransaction();
            
            List lstTransaction = session.createQuery("from Transactions").list();
            for (Iterator iterator = (Iterator) lstTransaction.iterator();iterator.hasNext();)
            {
                Transactions trans = (Transactions) iterator.next();
                if(trans.getTransactionDate().getTime()==date.getTime() && trans.getCardnumber().equals(cardnumber)) {
                    sum += trans.getAmount();
                }
            }
            transaction.commit();
        }
        catch (HibernateException e){
            transaction.rollback();
            e.printStackTrace();
        }
        finally {
            session.close();
        }
        return sum;
    }
    
    public static Integer SumAmountInADay(String merchantCode, Date date, String cardnumber) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = null;
        
        int sum = 0;
        List<Transactions> lst = new ArrayList<Transactions>();
        
        try {
            transaction = (Transaction) session.beginTransaction();
            
            List lstTransaction = session.createQuery("from Transactions").list();
            for (Iterator iterator = (Iterator) lstTransaction.iterator();iterator.hasNext();)
            {
                Transactions trans = (Transactions) iterator.next();
                if(trans.getTransactionDate().getTime()==date.getTime() && trans.getCardnumber().equals(cardnumber) && trans.getMerchantCode().equals(merchantCode)) {
                    sum += trans.getAmount();
                }
            }
            transaction.commit();
        }
        catch (HibernateException e){
            transaction.rollback();
            e.printStackTrace();
        }
        finally {
            session.close();
        }
        return sum;
    }
}
